package DB_Layer;
import Model_Layer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBProduct implements IFDBPro
{
	private Connection con;
	/** creates a new instances of DBProduct*/
	public DBProduct()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	
	
	//private methods
	//miscWhere is used whenever we want to select more than one product
	
	private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();
		
		String query = buildQuery(wClause);
			System.out.println("DbProduct " + query);
		
			try{// read from product
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		results = stmt.executeQuery(query);
		
		
		while( results.next() ){
			Product proObj = new Product();
		proObj = buildProduct(results);
			list.add(proObj);
		}// end while
			stmt.close();
//			if(retrieveAssociation)
//			{
//				IFDB_Supplier dbSupplier = new DB_Supplier();
//				for(Product proObj : list)
//				{
//					String SupName = proObj.getSupplier().getName();
//					Supplier supObj = dbSupplier.getSupplier(SupName, false);
//					proObj.setSupplier(supObj);
//				}
//			}
		}// end try
		catch(Exception e)
			{	
				System.out.println("Query exception - select: "+e);
				e.printStackTrace();
			}
			return list;
	}
	
			
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT ProID, name, countryOfOrigin, size, color, type, description, fabric, calibre, purchasePrice, salesPrice, rentPrice, minStock, stock, SupID  FROM product";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
		
		return query;
	}
	
	//SingleWhere is used when we only select one product
	private Product singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Product proObj = new Product();
		
			String query = buildQuery(wClause);
			System.out.println(query);
		try{// read the product from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				proObj = buildProduct(results);
				stmt.close();
//				if(retrieveAssociation)
//				{
//					
//					IFDB_Supplier dbSupplier = new DB_Supplier();
//					
//					String supName = proObj.getSupplier().getName();
//					Supplier supObj = dbSupplier.getSupplier(supName, false);
//					proObj.setSupplier(supObj);
//				}
			}
			else
			{
					proObj = null;
			}
			}
			catch(Exception e)
			{
				System.out.println("Query exception: "+e);
			}
			return proObj;
	}
	
	//Implements the methods from the interface
	// Get all products
	public ArrayList<Product> getAllProducts(boolean retriveAssociation) 
	{
		return miscWhere("", retriveAssociation);
	}

	//get one product having the name
	public Product findProduct(String proName, boolean retriveAssociation) 
	{
		String wClause = " Name like '%" + proName + "%'";
		System.out.println("SearchProduct " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	//insert a new product
	public int insertProduct(Product pro) throws Exception 
	{
		int nextproID = GetMaxID.getMaxID("Select max(proID from product");
		nextproID = nextproID + 1;
		System.out.println("next proID = " + nextproID);
		
		int rc = -1;
		String query="INSERT INTO product(ProID, Name, PurchasePrice, SalesPrice, RentPrice, CountryOfOrigin, MinStock, Stock, Size, Color, Type, Desciption, Fabric, Calibre, SupID)  VALUES('"+
				nextproID + "','" +
				pro.getName() + "','" +
				pro.getPurchasePrice() + "','" +
				pro.getSalesPrice() + "','" +
				pro.getRentPrice() + "','" +
				pro.getCountryOfOrigin() + "','" +
				pro.getMinStock() + "','" +
				pro.getStock() + "','" +
				pro.getSize() + "','" +
				pro.getColor() + "','" +
				pro.getType() + "','" +
				pro.getDescription() + "','" +
				pro.getFabric() + "','" +
				pro.getCalibre() + "','" +
				pro.getSupplier().getSupID() + "','";
				
		
		System.out.println("insert : " + query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException ex){
			System.out.println("Product ikke oprettet");
			throw new Exception ("Product is not inserted coorect");
		}
		return(rc);
				
	}
	
	public int delete(int proID)
	{
		int rc = -1;
		PreparedStatement pstmt = null;
		String delete = "delete from product where proID = ?";
		System.out.println(delete);
			try{
				pstmt = con.prepareStatement(delete);
				pstmt.setInt(1,proID);
				rc = pstmt.executeUpdate();
			}
			catch(SQLException sqlE)
			{
				System.out.println("SQL Error");
				System.out.println(sqlE.getMessage());
			}
			catch(Exception e)
			{
				e.getMessage();
			}
			return rc;
	
			}
	
	
	public int updateProduct(Product pro) 
	{
		Product proObj = pro;
		int rc=-1;
		
		String query="UPDATE product SET "+
				"Name ="+ proObj.getName()+"',"+
				"PurchasePrice ="+ proObj.getPurchasePrice()+",'"+
				"SalesPrice ="+ proObj.getSalesPrice()+",'"+
				"RentPrice ="+ proObj.getRentPrice()+",'"+
				"CountryOfOrigin ="+ proObj.getCountryOfOrigin()+",'"+
				"MinStock ="+ proObj.getMinStock()+",'"+
				"Stock ="+ proObj.getStock()+",'"+
				"Size ="+ proObj.getSize()+",'"+
				"Color ="+ proObj.getColor()+",'"+
				"Type ="+ proObj.getType()+",'"+
				"Description ="+ proObj.getDescription()+",'"+
				"Fabric ="+ proObj.getFabric()+",'"+
				"Calibre ="+ proObj.getCalibre()+",'"+
				" WHERE ProID = '"+ proObj.getProid() + "'";
				System.out.println("Update query:" + query);
			try{
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				
				stmt.close();
			}//end try
			catch(Exception ex){
				System.out.println("Update exception in product db: "+ex);
			}
			return(rc);
	}

	public Product searchProductID(int proID, boolean b) {
		return null;
	}
	
	private Product buildProduct (ResultSet resultset)
	{
		Product proObj = new Product ();
		
		try{
			proObj.setName(resultset.getString("Name"));
			proObj.setCountryOfOrigin(resultset.getString("CountryOfOrigin"));
			proObj.setSize(resultset.getString("Size"));
			proObj.setColor(resultset.getString("Color"));
			proObj.setType(resultset.getString("Type"));
			proObj.setDescription(resultset.getString("Description"));
			proObj.setFabric(resultset.getString("Fabric"));
			proObj.setCalibre(resultset.getString("Calibre"));
			proObj.setPurchasePrice(resultset.getDouble("PurchasePrice"));
			proObj.setSalesPrice(resultset.getDouble("SalesPrice"));
			proObj.setRentPrice(resultset.getDouble("RentPrice"));
			proObj.setProID(resultset.getInt("ProID"));
			proObj.setMinStock(resultset.getInt("MinStock"));
			proObj.setStock(resultset.getInt("Stock"));
			proObj.setSupplier(buildSupplier(resultset.getInt("SupID")));
			
			
			
		}
		catch(Exception e)
		{
			System.out.println("Error in building the Product object");
		}
		return proObj;
	}
	
	//Method for building a Supplier using the DB_Supplier class
	//Kan evt. laves i buildProject
	private Supplier buildSupplier(int supID)
	{
		DB_Supplier supDB = new DB_Supplier();
		Supplier supObj = new Supplier ();
		
		supObj = supDB.getSupplier(supID);
		return supObj;
		
		
	}

	public int delete(Product pro) {
		
		return 0;
	}

	
	

}
